Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

DataServer connection management

When a 4GL session is established, the DataServer for Microsoft SQL Server opens the number of read-only connections specified in the PRGRS_CACHE_CONN value. The DataServer maintains these connections for the duration of the 4GL session, reusing them for subsequent queries.

Enabling connection management

DataServer connection management is enabled and tuned with the PRGRS_CACHE_CONN option to the -Dsrv connection parameter. Connection management is enabled by default, and the default number of managed connections is 5.

Change the number of connections using the following syntax:

-Dsrv PRGRS_CACHE_CONN,n

Where n is the number of managed connections.

Disabling connection management

DataServer connection management is disabled by setting the number of managed connections to zero, as shown:

-Dsrv PRGRS_CACHE_CONN,0

Connections exclusively for stored procedures

When DataServer connection management is enabled, connections for stored procedures are allocated from the managed connections. If your stored procedures modify the connection environment or attributes, allocate a set of managed connections exclusively for stored procedures to prevent the modified connections from being reallocated for general use.

Allocate managed connections exclusively for stored procedures with the following syntax:

-Dsrv PRGRS_SP_CACHE_CONN,n

where n is the number of managed connections allocated to stored procedures. Set n to a value large enough to handle the largest number of stored procedures you can run simultaneously. If you exceed n, the connection will be allocated from the ODBC connection pool, if enabled, making the modified connection available for general reuse.

Note: This switch is off by default, and is only needed for applications that modify the connection environment or attributes within stored procedures.

Impact on commits in stored procedures

Running a stored procedure in a separate connection changes the timing of changes being committed to the data source. When a stored procedure is run in a separate connection, changes not explicitly committed during the execution of the stored procedure are committed at the time the procedure handle is closed and the connection is recycled.